package com.lijian.dao;

import com.lijian.pojo.Blog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface BlogDao extends JpaRepository<Blog, Long>, JpaSpecificationExecutor<Blog> {

    /**
     * 查询推荐的博客   只有推荐的博客才会被别人看到
     *
     * @param pageable
     * @return
     */
    @Query("select b from Blog b where b.recommend = true ")
    List<Blog> findTop(Pageable pageable);

    //同等于 select * from t_blog  where title like '%JAVA%' or content like '%JAVA%'
    //但是不会帮你加% 需要自己拼接
    @Query("select b from Blog b where b.title like ?1 or b.content like ?1")
    Page<Blog> findByQuery(String query, Pageable pageable);

    @Transactional
    @Modifying
    @Query("update Blog b set b.views = b.views + 1 where b.id = ?1")
    int updateViews(Long id);

    //等同于  SELECT DATE_FORMAT(b.update_time,'%Y') as year FROM t_blog b GROUP BY year ORDER BY year DESC;
    /*@Query("select function('date_format',b.updateTime,'%Y') as year from Blog b group by function('date_format',b.updateTime,'%Y') order by year desc")*/
    @Query(value = "SELECT DATE_FORMAT(b.update_time,'%Y') as year FROM t_blog b GROUP BY year ORDER BY year DESC",nativeQuery = true)
    List<String> findGroupYear();

    //SELECT * FROM t_blog b WHERE DATE_FORMAT(b.update_time,'%Y') = '2020'
    @Query("select b from Blog b where function('date_format',b.updateTime,'%Y') = ?1")
    List<Blog> findByYear(String year);
}